超过1000w条数据，MySQL查询越来越慢？四种方案帮你解决！

2024-06-11 12:08| 来源: 网络整理| 查看: 265

一般分页

在系统中需要进行分页操作时，我们通常会使用 LIMIT 加上偏移量的方式实现，语法格式如下。

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

在有对应索引的情况下，这种方式一般效率还不错。但它存在一个让人头疼的问题，在偏移量非常大的时候，也就是翻页到很靠后的页面时，查询速度会变得越来越慢。

我们来演示一下。

先创建一个订单表 t_order。

CREATE TABLE `t_order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `order_no` varchar(32) NOT NULL COMMENT '订单号', `user_id` varchar(20) NOT NULL COMMENT '用户ID', `amount` decimal(18,2) NOT NULL COMMENT '订单金额', `order_status` tinyint(4) NOT NULL COMMENT '订单状态：0新建 1处理中 2成功 3失败', `create_time` datetime NOT NULL COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `uniq_order_no` (`order_no`) USING BTREE COMMENT '订单号唯一索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

往表中插入1100w 条数据。（ t1 是一个有100条数据的表，这里我利用笛卡尔乘积的方式插入1100w条数据）

set @N=0; INSERT INTO t_order(`order_no`,`user_id`,`amount`,`order_status`,`create_time`) select CONCAT("APP", @N:=@N+1), CONCAT("USER_ID_", @N+1), @N%10000, @N%4, NOW() from t1 a, t1 b, t1 c, t1 d LIMIT 11000000;

我们看下，如下这些查询花费的时间。

select * from t_order order by id limit 0, 10; select * from t_order order by id limit 10000, 10; select * from t_order order by id limit 100000, 10; select * from t_order order by id limit 1000000, 10; select * from t_order order by id limit 10000000, 10;

执行时间如下：

-- 0.002 -- 0.045 -- 0.069 -- 0.517 -- 4.134

同样是只查询10条数据，最开始的时候查询花费 0.002s，而到最后，查询花费了 4.134s。这是什么原因呢？这是因为查询时 MySQL 并不是跳过 OFFSET 行，而是取 OFFSET+N 行，然后放弃前 OFFSET 行，最后返回 N 行，当 OFFSET 特别大的时候，效率就非常的低下。

拿 limit 10000, 10 这条语句来说明一下， MySQL在执行这条查询的时候，需要查询 10010 (10000 + 10) 条记录，然后只返回最后 10 条，并将前面的 10000 条记录抛弃，这样当翻页越靠后时，代价就变得越来越高。

知道问题所在了，那有什么办法可以优化，解决这个问题呢？

1优化一：记录位置，避免使用 OFFSET

首先获取第一页的结果：

select * from t_order limit 10;

假如上边返回的是 id 为1 ~ 10的记录，我们将 10 这个值记住，下一页查询就可以直接从 10 这个值开始。

select * from t_order where id > 10 limit 10;

这样做，无论翻页到多少页，性能都会很好：

select * from t_order limit 10; select * from t_order where id > 10000 limit 10; select * from t_order where id > 100000 limit 10; select * from t_order where id > 1000000 limit 10; select * from t_order where id > 10000000 limit 10;

执行时间如下：

-- 0.003 -- 0.005 -- 0.002 -- 0.002 -- 0.002

而如果我们当前记录的 id 值为 10000，我们想查上一页怎么办呢？返回去查一下即可：

select * from t_order where id = (select id from t_order order by id limit 0, 1) order by id limit 10; select * from t_order where id >= (select id from t_order order by id limit 10000, 1) order by id limit 10; select * from t_order where id >= (select id from t_order order by id limit 100000, 1) order by id limit 10; select * from t_order where id >= (select id from t_order order by id limit 1000000, 1) order by id limit 10; select * from t_order where id >= (select id from t_order order by id limit 10000000, 1) order by id limit 10;

执行时间如下：

-- 0.007 -- 0.009 -- 0.047 -- 0.332 -- 2.822

可以看到，这种优化方式也可以提升查询速度。这其实是利用了索引覆盖的如下好处：

索引文件不包含行数据的所有信息，故其大小远小于数据文件，因此可以减少大量的IO操作。索引覆盖只需要扫描一次索引树，不需要回表扫描行数据，所以性能比回表查询要高。 4优化四：使用索引覆盖+连接查询优化

这种优化方式跟优化三原理一样。也是先在索引上进行分页查询，当找到 id 后，再统一通过 JOIN 关联查询得到最终需要的数据详情。

select * from t_order a Join (select id from t_order order by id limit 0, 10) b ON a.id = b.id; select * from t_order a Join (select id from t_order order by id limit 10000, 10) b ON a.id = b.id; select * from t_order a Join (select id from t_order order by id limit 100000, 10) b ON a.id = b.id; select * from t_order a Join (select id from t_order order by id limit 1000000, 10) b ON a.id = b.id; select * from t_order a Join (select id from t_order order by id limit 10000000, 10) b ON a.id = b.id;

执行时间如下：

-- 0.001 -- 0.023 -- 0.028 -- 0.348 -- 2.955

【本文地址】

公司简介

联系我们